package guest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

public class Guest {
	
	String driver ="oracle.jdbc.driver.OracleDriver";
	String url ="jdbc:oracle:thin:@192.168.10.122:1521:xe";
	String db_user = "jobs";
	String db_pwd = "1234";
	
	Connection conn;
		
	GuestVo vo;
	

	public Guest() {
		try {
			//1) 드라이버 로딩
			Class.forName(driver).newInstance();
			
			//2) Connection 객체생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public Vector<GuestVo> list(){
		Vector<GuestVo> v = new Vector<>();
		String sql ="select * from guestbook where mid like ? or subject like ? or content like ?";
			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+vo.getFind()+"%");
				ps.setString(2, "%"+vo.getFind()+"%");
				ps.setString(3, "%"+vo.getFind()+"%");
				
				ResultSet rs = ps.executeQuery();
				while(rs.next()){
					GuestVo gv = new GuestVo();
				
					gv.setSerial(rs.getInt("serial"));
					gv.setMid(rs.getString("mid"));
					gv.setSubject(rs.getString("subject"));
					gv.setContent(rs.getString("content"));
					gv.setMdate(rs.getDate("mdate"));
					
					v.add(gv);
				}
				
				rs.close();ps.close();conn.close();
				
		} catch (Exception e) {
			e.printStackTrace();
		}
		return v;
	}
	
	public boolean insert(){
		boolean flag =false;
		String sql = "insert into guestbook(serial, mid, subject, content, mdate) values (gb_serial.nextval,?,?,?,sysdate)";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getMid());
			ps.setString(2, vo.getSubject());
			ps.setString(3, vo.getContent());
			
			int g = ps.executeUpdate();
			
			if(g>0){
				flag=true;
			}
			
			ps.close();conn.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public GuestVo view(){
		GuestVo gv = new GuestVo();
		String sql = "select * from guestbook where serial=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()){
				gv.setSerial(rs.getInt("serial"));
				gv.setMid(rs.getString("mid"));
				gv.setSubject(rs.getString("subject"));
				gv.setContent(rs.getString("content"));
				gv.setMdate(rs.getDate("mdate"));
				
			}
			rs.close();ps.close();conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return gv;
	}
	
	public boolean modify(){
		boolean flag = false;
		String sql = "update guestbook set subject=?, content=? where serial=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getSubject());
			ps.setString(2, vo.getContent());
			ps.setInt(3,vo.getSerial());
			
			int cnt = ps.executeUpdate();
			if(cnt>0) flag=true;
			
			ps.close();conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean delete(){
		boolean flag = false;
		String sql = "delete from guestbook where serial=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			int cnt = ps.executeUpdate();
			
			if(cnt>0) flag=true;
			
			ps.close();conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	public GuestVo getVo() {
		return vo;
	}

	public void setVo(GuestVo vo) {
		this.vo = vo;
	}
}
